﻿-- =============================================
-- Author:		Dorin Popa
-- Create date: 13.12.2009
-- Description:	Procedura pentru preluare numarului de pacienti din san
--				Serverurl SAN este definit ca si server legat
-- =============================================
CREATE PROCEDURE [dbo].[GetSanPacienti] 
	-- Add the parameters for the stored procedure here 281479271678954
	@dataProcesare datetime, 
	@dataOraProcesare datetime,
	@codBA bigint,
	@SanServer varchar(100)
AS
BEGIN
	set nocount on
	DECLARE @cmd  varchar(5000)
	
	DELETE FROM [IstoricPacienti] WHERE [Data]=@dataProcesare AND [Arhivat]=0
	
	SET @cmd='INSERT INTO [IstoricPacienti]
           ([Data],[DenSectie],[DenRegim],[NrPacienti],[NrInsotitori],[NrInvoiti])
		(SELECT '+CAST(@dataProcesare AS varchar(50)) + ',sectia,regimul,nr,coalesce(nr_insot,0) as nr_insot,invoiti from 		
		(SELECT      
		s.denumire as Sectia,
		s.cod_sect cod_sect,
		reg.denumire as regimul,
		cast(COUNT(i.cod_intern) AS int) as nr , 
		sum(case when r.varianta=2 then 1 else 0 end) 	invoiti 			
		FROM ' +  @SanServer +  '.San.dbo.V_MISCARE i, ' + 			
		@SanServer + '.San.dbo.v_REGIM_PACIENT r, ' + 
		@SanServer + '.San.dbo.regim reg, ' + 
		@SanServer + '.San.dbo.SECTII s  
		WHERE  s.cod_bloc_alimentar=@codBA and i.cod_intern = r.cod_intern and   
			(r.de_la <= '+ CAST(@dataProcesare as varchar(50)) + ') AND  r.cod_regim=reg.cod and i.cod_sect = s.cod_sect and
			(r.pana_la IS NULL OR r.pana_la >='+ CAST(@dataProcesare AS varchar(50)) + ') 		 and    
			(i.data_int <= ' +CAST(@dataOraProcesare as Varchar(50)) +') AND 
			(i.data_out IS NULL OR i.data_out > ' + CAST(@dataOraProcesare AS Varchar(50)) + ') 		
		GROUP BY  s.denumire,s.cod_sect,reg.denumire
	) a left outer join	
	(select 
		s.cod_sect,'' ''+denumire as denumire,
		count(*) as nr_insot 
		from '+@SanServer+'.San.dbo.v_miscare_insot mi,
		'+@SanServer+'.San.dbo.sectii s 			
		where s.cod_sect=mi.cod_sect and 			 
			(mi.data_int <= '+CAST(@dataOraProcesare as Varchar(50))+') AND 
			(mi.data_ext IS NULL OR mi.data_ext > '+CAST(@dataOraProcesare as Varchar(50))+') 			
		group by s.cod_sect,s.denumire) mii 
	on a.cod_sect=mii.cod_sect and a.regimul='' - MIXT'')'

EXEC(@cmd)

IF @@ERROR != 0 GOTO EROARE

GOTO Gata
EROARE:
	RAISERROR ('Perioada a fost arhivata si nu mai poate fi modificata!',16,-1)
	GOTO Gata
Gata:

END